#!/usr/bin/env node

/**
 * 修复缺失的预约数据脚本
 *
 * 从HTML快照和日志文件中恢复参考编号、预约日期、预约时间
 *
 * 使用方法：
 *   node app/scripts/repair-missing-booking-data.mjs
 *   node app/scripts/repair-missing-booking-data.mjs --db-path "C:\path\to\app.db"
 */

import { existsSync } from 'node:fs'
import { readdir, readFile } from 'node:fs/promises'
import { dirname, join } from 'node:path'
import { fileURLToPath } from 'node:url'
import * as cheerio from 'cheerio'

const __filename = fileURLToPath(import.meta.url)
const __dirname = dirname(__filename)
const projectRoot = join(__dirname, '..')

// 解析预约结果HTML
function parseBookingResultHtml(html) {
  try {
    const $ = cheerio.load(html)
    const data = {}

    // 解析参考编号（多种方式）
    // 方式1：从表格中提取
    $('tr').each((_, row) => {
      const cells = $(row).find('td, th')
      if (cells.length < 2)
        return
      const label = $(cells[0]).text().replace(/\s+/g, ' ').trim()
      const value = $(cells[1]).text().replace(/\s+/g, ' ').trim()
      if (!label || !value)
        return

      if (/參考編號|参考编号/.test(label)) {
        // 提取参考编号（移除空格和连字符）
        const refMatch = value.match(/([A-Z0-9\s-]{8,})/i)
        if (refMatch) {
          data.reference_number = (refMatch[1] || '').replace(/\s|-/g, '').trim()
        }
      }
    })

    // 方式2：从body文本中提取（作为备选）
    if (!data.reference_number) {
      const refText = $('body').text()
      const refMatch = refText.match(/(參考編號|参考编号)[：:\s]*([A-Z0-9\s-]{8,})/i)
      if (refMatch) {
        data.reference_number = (refMatch[2] || '').replace(/\s|-/g, '').trim()
      }
    }

    // 解析预约日期和时间
    $('tr').each((_, row) => {
      const cells = $(row).find('td, th')
      if (cells.length < 2)
        return
      const label = $(cells[0]).text().replace(/\s+/g, ' ').trim()
      const value = $(cells[1]).text().replace(/\s+/g, ' ').trim()
      if (!label || !value)
        return

      if (/預約日期及時間|预约日期及时间|預約日期|预约時間|预约时间/.test(label)) {
        // 解析日期：支持 "2026年1月12日" 或 "2026-01-12" 格式
        const dateMatch = value.match(/(\d{4})[年/-](\d{1,2})[月/-](\d{1,2})/)
        if (dateMatch) {
          const [, y, m, d] = dateMatch
          const mm = String(m).padStart(2, '0')
          const dd = String(d).padStart(2, '0')
          data.appointment_date = `${y}-${mm}-${dd}`
        }

        // 解析时间：支持 "上午 10:45" 或 "10:45" 格式
        const timeMatch = value.match(/(上午|下午|AM|PM)?\s*(\d{1,2}):(\d{2})/i)
        if (timeMatch) {
          const [, period, h, min] = timeMatch
          let hour = Number.parseInt(h)
          const periodStr = period || ''
          if ((periodStr === '下午' || periodStr.toUpperCase() === 'PM') && hour !== 12)
            hour += 12
          if ((periodStr === '上午' || periodStr.toUpperCase() === 'AM') && hour === 12)
            hour = 0
          data.appointment_time = `${String(hour).padStart(2, '0')}:${min}`
        }
      }
    })

    // 从隐藏字段中获取日期（优先使用，更可靠）
    const dateInput = $('input[name="storedAppointmentTimeslotDate"]').val()
    if (dateInput) {
      // 格式可能是 DD-MM-YYYY，转换为 YYYY-MM-DD
      const m = dateInput.match(/^(\d{2})-(\d{2})-(\d{4})$/)
      if (m) {
        const [, dd, mm, yyyy] = m
        data.appointment_date = `${yyyy}-${mm}-${dd}`
      }
      // 也可能是 YYYY-MM-DD 格式
      else if (/^\d{4}-\d{2}-\d{2}$/.test(dateInput)) {
        data.appointment_date = dateInput
      }
    }

    // 从隐藏字段获取时间（优先使用，更可靠）
    const fromTime = $('input[name="storedAppointmentTimeslotFromTime"]').val()
    if (fromTime && fromTime.length >= 4) {
      const hour = fromTime.slice(0, 2)
      const min = fromTime.slice(2, 4)
      data.appointment_time = `${hour}:${min}`
    }

    return data
  }
  catch (e) {
    console.error('解析HTML失败:', e.message)
    return {}
  }
}

// 从日志文件中提取预约成功的信息
async function extractFromLogs(logDir) {
  const results = []
  try {
    const files = await readdir(logDir)
    const logFiles = files.filter(f => f.startsWith('app-') && f.endsWith('.log'))

    for (const logFile of logFiles) {
      const logPath = join(logDir, logFile)
      const content = await readFile(logPath, 'utf-8')
      const lines = content.split('\n')

      for (const line of lines) {
        if (!line.trim())
          continue
        try {
          const log = JSON.parse(line)
          // 查找预约成功的记录
          if (log.message && /预约成功|预约流程成功/.test(log.message)) {
            const ref = log.referenceNumber || log['参考编号']
            const date = log['日期'] || log.selectedDate
            const time = log['时间'] || log.selectedTimeslot
            const passport = log.passport
            const name = log.userName || log['姓名']

            if (ref || date || time) {
              results.push({
                reference_number: ref,
                appointment_date: date ? normalizeDate(date) : undefined,
                appointment_time: time,
                passport_number: passport,
                full_name: name,
                timestamp: log.timestamp,
              })
            }
          }
        }
        catch {
          // 忽略无法解析的行
        }
      }
    }
  }
  catch (e) {
    console.warn('读取日志文件失败:', e.message)
  }

  return results
}

// 规范化日期格式
function normalizeDate(date) {
  if (!date)
    return undefined
  const s = String(date).trim()
  // 如果是 DD-MM-YYYY 格式，转换为 YYYY-MM-DD
  const m = s.match(/^(\d{2})-(\d{2})-(\d{4})$/)
  if (m) {
    const [, dd, mm, yyyy] = m
    return `${yyyy}-${mm}-${dd}`
  }
  // 如果已经是 YYYY-MM-DD 格式，直接返回
  if (/^\d{4}-\d{2}-\d{2}$/.test(s)) {
    return s
  }
  return s
}

// 扫描HTML快照目录
async function scanHtmlSnapshots(snapshotsDir) {
  const results = []

  if (!existsSync(snapshotsDir)) {
    console.warn(`快照目录不存在: ${snapshotsDir}`)
    return results
  }

  try {
    const dirs = await readdir(snapshotsDir, { withFileTypes: true })
    console.log(`  扫描到 ${dirs.length} 个子目录`)

    for (const dir of dirs) {
      if (!dir.isDirectory())
        continue

      const dirPath = join(snapshotsDir, dir.name)
      const metadataPath = join(dirPath, 'appointment-metadata.json')

      // 查找booking_08_submit_result.html文件（可能带时间戳前缀）
      let resultHtmlPath = null
      try {
        const files = await readdir(dirPath)
        // 查找包含 booking_08_submit_result 的HTML文件
        const resultFiles = files.filter(f =>
          f.endsWith('.html')
          && (f.includes('booking_08_submit_result') || f.includes('submit_result')),
        )
        if (resultFiles.length > 0) {
          // 优先使用精确匹配，否则使用第一个匹配的文件
          const exactMatch = resultFiles.find(f => f === 'booking_08_submit_result.html')
          resultHtmlPath = exactMatch ? join(dirPath, exactMatch) : join(dirPath, resultFiles[0])
        }
      }
      catch (e) {
        // 忽略读取目录错误
      }

      if (!resultHtmlPath || !existsSync(resultHtmlPath)) {
        continue
      }

      try {
        // 读取HTML文件
        const html = await readFile(resultHtmlPath, 'utf-8')
        const parsed = parseBookingResultHtml(html)

        if (!parsed.reference_number && !parsed.appointment_date && !parsed.appointment_time) {
          continue // 没有有效数据，跳过
        }

        // 读取元数据
        let metadata = {}
        if (existsSync(metadataPath)) {
          try {
            const metadataContent = await readFile(metadataPath, 'utf-8')
            metadata = JSON.parse(metadataContent)
          }
          catch {
            // 忽略元数据解析错误
          }
        }

        // 从目录名中提取信息（格式：日期_时间_序号_姓名）
        const dirMatch = dir.name.match(/^(.+?)_(.+?)_(.+?)_(.+)$/)
        const sequenceNumber = dirMatch ? dirMatch[3] : null
        const name = dirMatch ? dirMatch[4] : null

        if (parsed.reference_number || parsed.appointment_date || parsed.appointment_time) {
          results.push({
            appointment_id: metadata.appointmentId,
            sequence_number: metadata.sequenceNumber || sequenceNumber,
            passport_number: metadata.passportNumber,
            license_number: metadata.licenseNumber,
            full_name: metadata.fullName || name,
            reference_number: parsed.reference_number,
            appointment_date: parsed.appointment_date,
            appointment_time: parsed.appointment_time,
            dir_name: dir.name,
          })
        }
      }
      catch (e) {
        console.warn(`处理目录失败 ${dir.name}:`, e.message)
      }
    }
  }
  catch (e) {
    console.error('扫描快照目录失败:', e.message)
  }

  return results
}

// 主函数
async function main() {
  console.log('='.repeat(60))
  console.log('修复缺失的预约数据')
  console.log('='.repeat(60))
  console.log()

  // 解析命令行参数
  const args = process.argv.slice(2)
  let specifiedDbPath = null
  for (let i = 0; i < args.length; i++) {
    if (args[i] === '--db-path' && i + 1 < args.length) {
      specifiedDbPath = args[i + 1]
      break
    }
  }

  // 初始化数据库
  console.log('正在初始化数据库...')
  const { PrismaClient } = await import('@prisma/client')

  let dbPath = null

  // 如果用户指定了数据库路径，直接使用
  if (specifiedDbPath) {
    if (existsSync(specifiedDbPath)) {
      dbPath = specifiedDbPath
      console.log(`使用指定的数据库文件: ${dbPath}`)
    }
    else {
      console.error(`❌ 指定的数据库文件不存在: ${specifiedDbPath}`)
      process.exit(1)
    }
  }
  else {
    // 尝试多个可能的数据库路径（优先使用生产环境）
    const possibleDbPaths = []

    // 如果是Windows，优先使用生产环境路径
    if (process.platform === 'win32') {
      // 优先使用生产环境实际路径
      possibleDbPaths.push('C:\\Users\\Administrator\\AppData\\Roaming\\AutoTools\\data\\app.db')

      const appData = process.env.APPDATA || process.env.LOCALAPPDATA
      if (appData) {
        // 使用AutoTools（生产环境实际使用的路径）
        possibleDbPaths.push(join(appData, 'AutoTools', 'data', 'app.db'))
        // 兼容其他可能的路径
        possibleDbPaths.push(join(appData, 'auto-tools', 'data', 'app.db'))
      }
    }

    // 最后尝试开发环境：项目目录下的数据库
    possibleDbPaths.push(join(projectRoot, 'prisma', 'data', 'app.db'))

    // 查找存在的数据库文件（按优先级顺序）
    console.log('正在查找数据库文件（按优先级）...')
    for (const path of possibleDbPaths) {
      if (existsSync(path)) {
        dbPath = path
        console.log(`✅ 找到数据库文件: ${dbPath}`)
        break
      }
      else {
        console.log(`  ⚠️  不存在: ${path}`)
      }
    }

    if (!dbPath) {
      console.error('\n❌ 未找到数据库文件，尝试了以下路径:')
      possibleDbPaths.forEach(p => console.error(`  - ${p}`))
      console.error('\n提示: 可以使用 --db-path 参数指定数据库文件路径')
      console.error('例如: node app/scripts/repair-missing-booking-data.mjs --db-path "C:\\Users\\Administrator\\AppData\\Roaming\\AutoTools\\data\\app.db"')
      process.exit(1)
    }
  }

  const dbUrl = `file:${dbPath.replace(/\\/g, '/')}`
  process.env.DATABASE_URL = dbUrl

  const prisma = new PrismaClient({
    datasources: {
      db: { url: dbUrl },
    },
  })

  // 启用 WAL 模式
  try {
    await prisma.$executeRawUnsafe('PRAGMA journal_mode=WAL;')
    await prisma.$executeRawUnsafe('PRAGMA synchronous=NORMAL;')
    await prisma.$executeRawUnsafe('PRAGMA busy_timeout=15000;')
  }
  catch {}

  console.log(`✅ 数据库初始化完成: ${dbPath}`)
  console.log()

  try {
    // 扫描HTML快照（在app目录下）
    const snapshotsDir = join(projectRoot, 'request-run-snapshots')
    console.log(`正在扫描HTML快照目录: ${snapshotsDir}`)
    if (!existsSync(snapshotsDir)) {
      console.warn(`⚠️  HTML快照目录不存在: ${snapshotsDir}`)
    }
    const htmlData = await scanHtmlSnapshots(snapshotsDir)
    console.log(`✅ 找到 ${htmlData.length} 个HTML快照记录`)
    console.log()

    // 扫描日志文件（在项目根目录下）
    const logsDir = join(projectRoot, '..', 'logs')
    console.log(`正在扫描日志目录: ${logsDir}`)
    if (!existsSync(logsDir)) {
      console.warn(`⚠️  日志目录不存在: ${logsDir}`)
    }
    const logData = await extractFromLogs(logsDir)
    console.log(`✅ 找到 ${logData.length} 条日志记录`)
    console.log()

    // 先统计总记录数和各种状态的记录数（仅查询 appointment_management 表）
    console.log('📊 查询 appointment_management 表统计信息...')
    const totalCount = await prisma.appointment_management.count()
    const totalBooked = await prisma.appointment_management.count({
      where: { appointment_status: 'booked' },
    })
    const totalPending = await prisma.appointment_management.count({
      where: { appointment_status: 'pending' },
    })
    const totalFailed = await prisma.appointment_management.count({
      where: { appointment_status: 'failed' },
    })
    console.log(`📊 appointment_management 表统计:`)
    console.log(`  - 总记录数: ${totalCount}`)
    console.log(`  - booked状态: ${totalBooked}`)
    console.log(`  - pending状态: ${totalPending}`)
    console.log(`  - failed状态: ${totalFailed}`)

    // 如果总记录数过多，提醒用户可能查询到了错误的数据库
    if (totalCount > 1000) {
      console.log(`\n⚠️  警告: 记录数较多(${totalCount}条)，请确认是否使用了正确的数据库文件`)
      console.log(`   当前数据库: ${dbPath}`)
    }

    // 查找需要修复的记录（仅查询 appointment_management 表中 booked 状态的记录，包括占号数据）
    console.log('\n正在查找需要修复的数据库记录（appointment_management 表，booked状态，包括占号数据）...')

    // 先检查有多少条记录缺少各个字段（包括占号数据）
    // 使用原生SQL查询，避免Prisma的AND/OR嵌套问题
    const missingRefResult = await prisma.$queryRawUnsafe(`
      SELECT COUNT(*) as count FROM appointment_management 
      WHERE appointment_status = 'booked' 
        AND (reference_number IS NULL OR reference_number = '');
    `)
    const missingDateResult = await prisma.$queryRawUnsafe(`
      SELECT COUNT(*) as count FROM appointment_management 
      WHERE appointment_status = 'booked' 
        AND appointment_date IS NULL;
    `)
    const missingTimeResult = await prisma.$queryRawUnsafe(`
      SELECT COUNT(*) as count FROM appointment_management 
      WHERE appointment_status = 'booked' 
        AND appointment_time IS NULL;
    `)

    const missingRef = Array.isArray(missingRefResult) && missingRefResult[0] ? Number(missingRefResult[0].count) : 0
    const missingDate = Array.isArray(missingDateResult) && missingDateResult[0] ? Number(missingDateResult[0].count) : 0
    const missingTime = Array.isArray(missingTimeResult) && missingTimeResult[0] ? Number(missingTimeResult[0].count) : 0
    console.log(`  - 缺少参考编号: ${missingRef} 条`)
    console.log(`  - 缺少预约日期: ${missingDate} 条`)
    console.log(`  - 缺少预约时间: ${missingTime} 条`)

    // 使用原生SQL查询，避免Prisma的AND/OR嵌套问题（包括占号数据）
    const appointmentsRaw = await prisma.$queryRawUnsafe(`
      SELECT 
        id, reference_number, appointment_date, appointment_time,
        passport_number, license_number, full_name, sequence_number, is_placeholder
      FROM appointment_management 
      WHERE appointment_status = 'booked' 
        AND (
          reference_number IS NULL 
          OR reference_number = '' 
          OR appointment_date IS NULL 
          OR appointment_time IS NULL
        )
      LIMIT 10000;
    `)

    // 转换为Prisma格式
    const appointments = Array.isArray(appointmentsRaw)
      ? appointmentsRaw.map(r => ({
          id: Number(r.id),
          reference_number: r.reference_number,
          appointment_date: r.appointment_date,
          appointment_time: r.appointment_time,
          passport_number: r.passport_number,
          license_number: r.license_number,
          full_name: r.full_name,
          sequence_number: r.sequence_number,
          is_placeholder: r.is_placeholder,
        }))
      : []
    console.log(`✅ 找到 ${appointments.length} 条需要修复的记录`)

    // 显示前几条记录的详细信息（用于调试）
    if (appointments.length > 0) {
      console.log('\n需要修复的记录示例:')
      appointments.slice(0, 5).forEach((apt) => {
        console.log(`  ID: ${apt.id}, 姓名: ${apt.full_name || '未知'}, 参考编号: ${apt.reference_number || '(空)'}, 日期: ${apt.appointment_date || '(空)'}, 时间: ${apt.appointment_time || '(空)'}`)
      })
      if (appointments.length > 5) {
        console.log(`  ... 还有 ${appointments.length - 5} 条记录`)
      }
    }
    console.log()

    if (appointments.length === 0) {
      console.log('没有需要修复的记录')
      return
    }

    // 统计数据
    let repaired = 0
    let skipped = 0
    let failed = 0

    // 开始修复
    console.log('开始修复...')
    console.log()

    for (const apt of appointments) {
      try {
        const updateData = {}
        let found = false

        // 优先从HTML快照中查找（更准确）
        for (const html of htmlData) {
          let matched = false

          // 通过appointment_id匹配
          if (html.appointment_id && html.appointment_id === apt.id) {
            matched = true
          }
          // 通过序号匹配
          else if (html.sequence_number && apt.sequence_number
            && html.sequence_number === apt.sequence_number) {
            matched = true
          }
          // 通过护照号匹配（部分匹配）
          else if (html.passport_number && apt.passport_number) {
            const htmlPassport = html.passport_number.replace(/\*/g, '').replace(/\s/g, '')
            const aptPassport = apt.passport_number.replace(/\s/g, '')
            if (htmlPassport && aptPassport && aptPassport.includes(htmlPassport)) {
              matched = true
            }
          }
          // 通过姓名匹配
          else if (html.full_name && apt.full_name
            && html.full_name === apt.full_name) {
            matched = true
          }

          if (matched) {
            if (!apt.reference_number && html.reference_number) {
              updateData.reference_number = html.reference_number
              found = true
            }
            if (!apt.appointment_date && html.appointment_date) {
              updateData.appointment_date = html.appointment_date
              found = true
            }
            if (!apt.appointment_time && html.appointment_time) {
              updateData.appointment_time = html.appointment_time
              found = true
            }

            if (found) {
              console.log(`  [HTML] 预约 #${apt.id} (${apt.full_name || '未知'})`)
              break
            }
          }
        }

        // 如果HTML快照中没有找到，尝试从日志中查找
        if (!found) {
          for (const log of logData) {
            let matched = false

            // 通过护照号匹配
            if (log.passport_number && apt.passport_number
              && log.passport_number === apt.passport_number) {
              matched = true
            }
            // 通过姓名匹配
            else if (log.full_name && apt.full_name
              && log.full_name === apt.full_name) {
              matched = true
            }

            if (matched) {
              if (!apt.reference_number && log.reference_number) {
                updateData.reference_number = log.reference_number
                found = true
              }
              if (!apt.appointment_date && log.appointment_date) {
                updateData.appointment_date = log.appointment_date
                found = true
              }
              if (!apt.appointment_time && log.appointment_time) {
                updateData.appointment_time = log.appointment_time
                found = true
              }

              if (found) {
                console.log(`  [日志] 预约 #${apt.id} (${apt.full_name || '未知'})`)
                break
              }
            }
          }
        }

        // 执行更新
        if (Object.keys(updateData).length > 0) {
          await prisma.appointment_management.update({
            where: { id: apt.id },
            data: updateData,
          })
          console.log(`    ✅ 已更新: ${Object.keys(updateData).join(', ')}`)
          repaired++
        }
        else {
          console.log(`  ⏭️  跳过: 预约 #${apt.id} (${apt.full_name || '未知'}) - 未找到匹配数据`)
          skipped++
        }
      }
      catch (e) {
        console.error(`  ❌ 失败: 预约 #${apt.id} - ${e.message}`)
        failed++
      }
    }

    console.log()
    console.log('='.repeat(60))
    console.log('修复完成')
    console.log('='.repeat(60))
    console.log(`成功修复: ${repaired} 条`)
    console.log(`跳过: ${skipped} 条`)
    console.log(`失败: ${failed} 条`)
    console.log(`总计: ${appointments.length} 条`)
  }
  finally {
    await prisma.$disconnect()
    console.log('✅ 数据库连接已关闭')
  }
}

// 运行主函数
main().catch((e) => {
  console.error('脚本执行失败:', e)
  process.exit(1)
})
